﻿using DotNet.Utilities;
using JinfuyuanProject.Entity;
using JinfuyuanProject.Entity.Filter;
using JinfuyuanProject.Entity.Models;
using JinfuyuanProject.Entity.ViewModels;
using JinfuyuanProject.Repository;
using JinfuyuanProject.Repository.Interface;
using JinfuyuanProject.Service.Interface;
using MongoDB.Driver.Linq;
using NPOI.HSSF.Record;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Diagnostics.Contracts;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace JinfuyuanProject.Service
{
    /*
*  
* 母子涵
* 2025-04
*/
    public class PackageServicesService : IPackageServicesService
    {
        private readonly IPackageServicesRepository _packageServicesRepository;
        private readonly JinfuyuanDbContext _dbContext;
        private readonly IContractRepository _contractRepository;
        private readonly IPackageRepository _packageRepository;
        private readonly IParturientRepository _parturientRespository;
        private readonly HttpContextBase _httpContext;
        IQueryable<PackageSerVM> Records;
        public PackageServicesService(IPackageServicesRepository packageServicesRepository, JinfuyuanDbContext dbContext, IContractRepository contractRepository, IPackageRepository packageRepository, IParturientRepository parturientRepository, HttpContextBase httpContext)
        {
            _packageServicesRepository = packageServicesRepository;
            _dbContext = dbContext;
            _contractRepository = contractRepository;
            _packageRepository = packageRepository;
            _parturientRespository = parturientRepository;
            _httpContext = httpContext;
        }
        #region 基础方法
        public bool Delete(PackageServices packageServices)
        {
            return _packageServicesRepository.Delete(packageServices);
        }

        public IQueryable<PackageServices> GetList()
        {
            return _packageServicesRepository.GetList();
        }

        public PackageServices GetModel(int Id)
        {
            return _packageServicesRepository.GetModel(Id);
        }

        public int Insert(PackageServices packageServices)
        {
            return _packageServicesRepository.Insert(packageServices);
        }
        public bool Update(PackageServices packageServices)
        {
            return _packageServicesRepository.Update(packageServices);
        }
        public IEnumerable<T> QueryBySql<T>(string sql)
        {
            return _packageServicesRepository.QueryBySql<T>(sql);
        }

        #endregion
        #region 业务方法

        //批量删除
        public bool DeleteBatch(List<PackageServices> packageServicesList)
        {
            return _packageServicesRepository.DeleteBatch(packageServicesList);
        }
        //批量插入
        public int InsertBatch(List<PackageServices> packageservicesList)
        {
            return _packageServicesRepository.InsertBatch(packageservicesList);
        }

        //zy
        //套餐内服务
        public Hashtable QueryInBound(PackageSpendFilter filter)
        {
            int count;
            int contractId = filter.Id;
            //使用SQL查询套餐内服务记录（包含关联表）
            string Sql = $@"
             select sd.Id, s.ServiceName,s.ServicePrice,st.Name,sd.FeedBack, sd.ActualTime from ServiceDo as sd
                    left join Contract as c on c.Id=sd.Cid
	                left join Parturient as pa on pa.Id=c.ParturientId
                    left join Services as s on s.Id=sd.SId
                    left join Staff as st on st.Id=sd.Principal
	                where c.Id={contractId}";

            var Records = QueryBySql<PackageSerVM>(Sql).AsQueryable();

            Hashtable hashtable = new Hashtable();
            hashtable["code"] = 0;
            hashtable["message"] = "success";
            hashtable["data"] = MakeQueryFilter(Records, filter, out count);
            hashtable["count"] = count;//结束惰性加载

            return hashtable;
        }
        //套餐外消费
        public Hashtable QueryOutBound(PackageSpendFilter filter)
        {
            int count;
            int contractId = filter.Id;
            //使用SQL查询套餐外服务记录（包含关联表）
            string Sql = $@"
             SELECT DISTINCT er.*, COALESCE(p.ProductName, s.ServiceName) AS SpendName, sd.ActualTime
FROM ExpenseRecord AS er
LEFT JOIN Contract AS c ON c.Id = er.Cid
LEFT JOIN Parturient AS pa ON pa.Id = c.ParturientId
LEFT JOIN Product AS p ON p.Id = CASE WHEN er.type = 1 THEN er.ConsId ELSE NULL END
LEFT JOIN Services AS s ON s.Id = CASE WHEN er.type = 2 THEN er.ConsId ELSE NULL END
LEFT JOIN ServiceDo AS sd ON sd.Sid = s.Id AND sd.Cid = er.Cid
WHERE c.Id ={contractId}";
            var Records = QueryBySql<PackageSerVM>(Sql).AsQueryable();

            Hashtable hashtable = new Hashtable();
            hashtable["code"] = 0;
            hashtable["message"] = "success";
            hashtable["data"] = MakeQueryFilter(Records, filter, out count);
            hashtable["count"] = count;//结束惰性加载

            return hashtable;
        }

        //清单数据
        public Hashtable QueryList(PackageSpendFilter filter)
        {
            int count;
            int contractId = filter.Id;
            //使用SQL查询套餐外服务记录（包含关联表）
            string Sql = $@"
                          SELECT sd.Id,s.ServicePrice AS price,sd.ActualTime,s.ServiceName AS SpendName,0 AS Type 
                FROM ServiceDo as sd
                LEFT JOIN 
                Contract as c ON c.Id = sd.Cid
                LEFT JOIN 
                Services as s ON s.Id = sd.SId
                WHERE 
                c.Id ={contractId}
                UNION ALL
                SELECT er.Id,er.price,COALESCE(sd.ActualTime, er.CreateTime) AS ActualTime,COALESCE(p.ProductName, s.ServiceName) AS SpendName,1 AS Type
								FROM 
								ExpenseRecord AS er
								LEFT JOIN 
								Contract AS c ON c.Id = er.Cid
								-- 根据 type 为 1 连接 Product 表
								LEFT JOIN 
								Product AS p ON p.Id = CASE WHEN er.type = 1 THEN er.ConsId ELSE NULL END
								-- 根据 type 为 2 连接 Services 表
								LEFT JOIN 
								Services AS s ON s.Id = CASE WHEN er.type = 2 THEN er.ConsId ELSE NULL END
								LEFT JOIN
								ServiceDo AS sd ON sd.Id=s.Id AND sd.Cid=er.Cid
								 WHERE 
								c.Id ={contractId}";
            Records = QueryBySql<PackageSerVM>(Sql).AsQueryable();

            Hashtable hashtable = new Hashtable();
            hashtable["code"] = 0;
            hashtable["message"] = "success";
            hashtable["data"] = MakeQueryFilter(Records, filter, out count);
            hashtable["count"] = count;//结束惰性加载

            return hashtable;
        }
        //拼接excel
        public Hashtable GenerateExcel(PackageSpendFilter filter)
        {
            Hashtable result = new Hashtable();
            try
            {
                // 创建一个工作簿
                IWorkbook workbook = new XSSFWorkbook();

                // 创建一个工作表
                ISheet sheet = workbook.CreateSheet("Sheet1");
                IRow row0 = sheet.CreateRow(0);

                // 定义合并区域 (从第0行到第4行, 第0列到第8列)
                CellRangeAddress cellRange = new CellRangeAddress(0, 3, 0, 7);
                sheet.AddMergedRegion(cellRange);
                ICell cell = row0.CreateCell(0);
                cell.SetCellValue("服务清单");

                // 自定义样式应用到合并后的单元格
                ICellStyle style = workbook.CreateCellStyle();

                // 设置字体为宋体，字号为三号（对应磅值 16）
                IFont font = workbook.CreateFont();
                font.FontName = "宋体";
                font.FontHeightInPoints = 20;
                style.SetFont(font);

                // 设置水平居中对齐
                style.Alignment = HorizontalAlignment.Center;
                // 设置垂直居中对齐
                style.VerticalAlignment = VerticalAlignment.Center;

                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                style.BottomBorderColor = IndexedColors.Black.Index;
                style.LeftBorderColor = IndexedColors.Black.Index;
                style.RightBorderColor = IndexedColors.Black.Index;
                style.TopBorderColor = IndexedColors.Black.Index;

                for (int i = cellRange.FirstRow; i <= cellRange.LastRow; i++)
                {
                    IRow row = sheet.GetRow(i) ?? sheet.CreateRow(i);
                    for (int j = cellRange.FirstColumn; j <= cellRange.LastColumn; j++)
                    {
                        ICell singleCell = row.GetCell(j) ?? row.CreateCell(j);
                        singleCell.CellStyle = style;
                    }
                }
                cell.CellStyle = style;
                // 自定义样式应用到合并后的单元格
                ICellStyle style1 = workbook.CreateCellStyle();

                style1.BorderBottom = BorderStyle.Thin;
                style1.BorderLeft = BorderStyle.Thin;
                style1.BorderRight = BorderStyle.Thin;
                style1.BorderTop = BorderStyle.Thin;
                style1.BottomBorderColor = IndexedColors.Black.Index;
                style1.LeftBorderColor = IndexedColors.Black.Index;
                style1.RightBorderColor = IndexedColors.Black.Index;
                style1.TopBorderColor = IndexedColors.Black.Index;

                //第五六行数据
                CellRangeAddress FirstTwoColumns5 = new CellRangeAddress(4, 5, 0, 0);
                sheet.AddMergedRegion(FirstTwoColumns5);
                CellRangeAddress SecondTwoColumns5 = new CellRangeAddress(4, 5, 1, 2);
                sheet.AddMergedRegion(SecondTwoColumns5);
                CellRangeAddress threeTwoColumns5 = new CellRangeAddress(4, 5, 3, 3);
                sheet.AddMergedRegion(threeTwoColumns5);
                CellRangeAddress fourTwoColumns5 = new CellRangeAddress(4, 5, 4, 5);
                sheet.AddMergedRegion(fourTwoColumns5);
                CellRangeAddress fiveTwoColumns5 = new CellRangeAddress(4, 5, 6, 6);
                sheet.AddMergedRegion(fiveTwoColumns5);
                CellRangeAddress sixTwoColumns5 = new CellRangeAddress(4, 5, 7, 7);
                sheet.AddMergedRegion(sixTwoColumns5);
                var contract = _contractRepository.GetModel(filter.Id);
                var par = _packageRepository.GetModel(contract.PackageId);
                var paruient = _parturientRespository.GetModel(contract.ParturientId);
                IRow row5 = sheet.GetRow(4) ?? sheet.CreateRow(4);
                ICell cell50 = row5.CreateCell(0);
                ICell cell53 = row5.CreateCell(3);
                ICell cell55 = row5.CreateCell(6);
                cell50.SetCellValue("产妇名称：");
                row5.CreateCell(1).SetCellValue(paruient.ParturientName);
                cell53.SetCellValue("合同编号：");
                row5.CreateCell(4).SetCellValue(contract.ContractNumber);
                cell55.SetCellValue("套餐名称：");
                row5.CreateCell(7).SetCellValue(par.PackageName);
                // 定义一个合并区域数组，方便遍历
                CellRangeAddress[] mergedRegions = { FirstTwoColumns5, SecondTwoColumns5, threeTwoColumns5,
                    fourTwoColumns5, fiveTwoColumns5, sixTwoColumns5
                    };

                // 遍历每个合并区域
                foreach (CellRangeAddress region in mergedRegions)
                {
                    for (int rowIndex = region.FirstRow; rowIndex <= region.LastRow; rowIndex++)
                    {
                        IRow row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                        for (int colIndex = region.FirstColumn; colIndex <= region.LastColumn; colIndex++)
                        {
                            ICell newcell = row.GetCell(colIndex) ?? row.CreateCell(colIndex);
                            newcell.CellStyle = style1;
                        }
                    }
                }
                // 第七八行数据
                CellRangeAddress FirstTwoColumns7 = new CellRangeAddress(6, 7, 0, 0);
                sheet.AddMergedRegion(FirstTwoColumns7);
                CellRangeAddress SecondTwoColumns7 = new CellRangeAddress(6, 7, 1, 2);
                sheet.AddMergedRegion(SecondTwoColumns7);
                CellRangeAddress threeTwoColumns7 = new CellRangeAddress(6, 7, 3, 3);
                sheet.AddMergedRegion(threeTwoColumns7);
                CellRangeAddress fourTwoColumns7 = new CellRangeAddress(6, 7, 4, 5);
                sheet.AddMergedRegion(fourTwoColumns7);
                CellRangeAddress fiveTwoColumns7 = new CellRangeAddress(6, 7, 6, 6);
                sheet.AddMergedRegion(fiveTwoColumns7);
                CellRangeAddress sixTwoColumns7 = new CellRangeAddress(6, 7, 7, 7);
                sheet.AddMergedRegion(sixTwoColumns7);

                // 创建第七行（索引为 6）
                IRow row7 = sheet.GetRow(6) ?? sheet.CreateRow(6);
                ICell cell70 = row7.CreateCell(0);
                ICell cell73 = row7.CreateCell(3);  // 出院时间应该在这个位置
                ICell cell74 = row7.CreateCell(6);
                cell70.SetCellValue("入住时间：");
                row7.CreateCell(1).SetCellValue(contract.LiveTime.ToString());
                cell73.SetCellValue("出院时间：");  // 修正赋值位置
                row7.CreateCell(4).SetCellValue(DateTime.Now.ToString());
                Staff staff = _httpContext.Session["user"] as Staff;
                cell74.SetCellValue("办理人：");
                row7.CreateCell(7).SetCellValue(staff.Name);
                // 定义一个合并区域数组，方便遍历
                CellRangeAddress[] mergedRegions7 = {
                FirstTwoColumns7, SecondTwoColumns7, threeTwoColumns7,
                fourTwoColumns7, fiveTwoColumns7, sixTwoColumns7
                };

                // 遍历每个合并区域
                foreach (CellRangeAddress region in mergedRegions7)
                {
                    for (int rowIndex = region.FirstRow; rowIndex <= region.LastRow; rowIndex++)
                    {
                        IRow row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                        for (int colIndex = region.FirstColumn; colIndex <= region.LastColumn; colIndex++)
                        {
                            ICell newcell1 = row.GetCell(colIndex) ?? row.CreateCell(colIndex);
                            newcell1.CellStyle = style1;
                        }
                    }
                }
                // 第九行数据
                string[] headersRow9 = { "消费名目", "实施时间", "服务类型", "金额" };
                int colIndexRow9 = 0;
                for (int i = 0; i < headersRow9.Length; i++)
                {
                    string header = headersRow9[i];
                    IRow row9 = sheet.GetRow(8) ?? sheet.CreateRow(8);
                    ICell cell9 = row9.CreateCell(colIndexRow9);
                    cell9.SetCellValue(header);
                    CellRangeAddress cellRange9 = new CellRangeAddress(8, 8, colIndexRow9, colIndexRow9 + 1);
                    sheet.AddMergedRegion(cellRange9);
                    // 为合并区域的单元格应用样式
                    for (int k = cellRange9.FirstRow; k <= cellRange9.LastRow; k++)
                    {
                        IRow row = sheet.GetRow(k) ?? sheet.CreateRow(k);
                        for (int j = cellRange9.FirstColumn; j <= cellRange9.LastColumn; j++)
                        {
                            ICell singleCell = row.GetCell(j) ?? row.CreateCell(j);
                            singleCell.CellStyle = style1;
                        }
                    }
                    colIndexRow9 += 2;
                }
                // 调用 QueryList(filter) 方法获取数据
                Hashtable data = QueryList(filter);
                List<PackageSerVM> packageSerVMList = (data["data"] as IEnumerable<PackageSerVM>).ToList();
                int startRowIndex = 9; // 数据从第10行开始（索引9）
                foreach (PackageSerVM item in packageSerVMList)
                {
                    IRow dataRow = sheet.GetRow(startRowIndex) ?? sheet.CreateRow(startRowIndex);

                    // 填充"消费名目"（合并A:B列）
                    dataRow.CreateCell(0).SetCellValue(item.SpendName);
                    sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex, 0, 1));

                    // 填充"实施时间"（合并C:D列）
                    dataRow.CreateCell(2).SetCellValue(item.ActualTime.ToString());
                    sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex, 2, 3));

                    // 填充"服务类型"（合并E:F列）
                    string serviceTypeText = item.Type == 1 ? "套餐外" : "套餐内";
                    dataRow.CreateCell(4).SetCellValue(serviceTypeText);
                    sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex, 4, 5));

                    // 填充"金额"（合并G:H列）
                    dataRow.CreateCell(6).SetCellValue((double)item.Price);
                    sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex, 6, 7));

                    // 应用样式（假设使用style1）
                    for (int j = 0; j < headersRow9.Length * 2; j += 2)
                    {
                        for (int k = j; k <= j + 1; k++)
                        {
                            ICell cellnew = dataRow.GetCell(k) ?? dataRow.CreateCell(k);
                            cellnew.CellStyle = style1;
                        }
                    }

                    startRowIndex++;
                }
                // 计算套餐外总金额
                double extraPackageTotal = packageSerVMList.Where(x => x.Type == 1).Sum(x => (double)x.Price);

                // 第十行：套餐外消费总金额
                IRow row10 = sheet.GetRow(startRowIndex) ?? sheet.CreateRow(startRowIndex);

                // 合并前两列并设置标签
                var cell10_0 = row10.CreateCell(0);
                cell10_0.SetCellValue("套餐外消费总金额");
                cell10_0.CellStyle = style1;
                sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex, 0, 1));
                for (int i = 1; i <= 2; i++)
                {
                    var cellfir = row10.CreateCell(i);
                    cellfir.CellStyle = style1;
                }
                // 合并后六列并设置金额值
                var cell10_2 = row10.CreateCell(2);
                cell10_2.SetCellValue(extraPackageTotal.ToString("N2") + "元");
                cell10_2.CellStyle = style1;
                sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex, 2, 7));

                // 为合并区域的其他单元格也设置样式
                for (int i = 3; i <= 7; i++)
                {
                    var cellone = row10.CreateCell(i);
                    cellone.CellStyle = style1;
                }

                startRowIndex++;

                // 第十一行：打印时间
                IRow row11 = sheet.GetRow(startRowIndex) ?? sheet.CreateRow(startRowIndex);

                // 合并前两列并设置标签
                var cell11_0 = row11.CreateCell(0);
                cell11_0.SetCellValue("打印时间");
                cell11_0.CellStyle = style1;
                sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex, 0, 1));

                for (int i = 1; i <= 2; i++)
                {
                    var cellthree = row11.CreateCell(i);
                    cellthree.CellStyle = style1;
                }
                // 合并后六列并设置当前时间
                var cell11_2 = row11.CreateCell(2);
                cell11_2.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日 HH:mm:ss"));
                cell11_2.CellStyle = style1;
                sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, startRowIndex, 2, 7));

                // 为合并区域的其他单元格也设置样式
                for (int i = 3; i <= 7; i++)
                {
                    var celltwo = row11.CreateCell(i);
                    celltwo.CellStyle = style1;
                }

                // 1. 从配置获取路径
                string basePath = ConfigurationManager.AppSettings["excelPath"];

                // 2. 验证路径
                if (!Directory.Exists(basePath))
                {
                    Directory.CreateDirectory(basePath);
                }

                // 3. 更安全的路径组合方式
               
                string fileName = $"{filter.Id}_{paruient.ParturientName}_服务清单.xlsx";
                string fullPath = basePath + fileName;

                //// 4. 确保目录存在（使用完整路径中的目录部分）
                //string directory = Path.GetDirectoryName(fullPath);
                //Directory.CreateDirectory(directory);

                // 如果文件已存在则删除
                if (File.Exists(fullPath))
                {
                    File.Delete(fullPath);
                }
                // 5. 保存工作簿
                using (FileStream fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                }
                // 3. 返回成功标识
                result["code"] = 0;
                result["message"] = "success";
                result["filePath"] = fullPath; // 可选：返回文件路径
                result["fileName"] = fileName;
                // 关闭工作簿
                workbook.Close();
                // 返回文件给浏览器
                
            }
            catch (Exception ex)
            {
                Console.WriteLine($"发生错误：{ex.Message}");
            }

            return result;
        }
        //点击退房的操作
        //Contract .SignState=2已退房
        //Contract.Deposit=0合同押金清零
        //Home.state=0
        public Hashtable CheckOut(int contractId)
        {
            var result = new Hashtable();
            using (var transaction = _dbContext.Database.BeginTransaction())
            {
                try
                {
                    var contract = _dbContext.Contract.Find(contractId);
                    if (contract == null)
                    {
                        result["code"] = 1;
                        result["message"] = "参数不能为空";
                        return result;
                    }
                    contract.SignState = 2;

                    //如果押金还有剩余，就在一条出账记录，如果押金为负数，就在添加一条入账记录，然后押金清零
                    // 4. 处理押金和账目
                    if (contract.Deposit != 0)
                    {
                        var entry = new Entry
                        {
                            ContractId = contractId,
                            EntryPrice = contract.Deposit,
                            Describe = "退房押金",
                            EntryPeopleId = 1,
                            VerifyId = 1,
                            CreateTime = DateTime.Now,
                            IsDelete = 0,
                            Type = contract.Deposit > 0 ? 0 : 1 // 出账为0，入账为1
                        };
                        _dbContext.Entry.Add(entry);
                    }

                    // 5. 押金清零
                    contract.Deposit = 0;
                    var home = _dbContext.Home.Find(contract.HomeId);
                    if (home == null)
                    {
                        throw new Exception("没找到");
                    }
                    home.State = 0;
                    transaction.Commit();
                    _dbContext.SaveChanges();

                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                }
                result["code"] = 0;
                result["message"] = "success";
                return result;
            }
        }


        public IQueryable<PackageSerVM> MakeQueryFilter(IQueryable<PackageSerVM> packageSerVMs, PackageSpendFilter packageSpendFilter, out int count)
        {
            if (packageSpendFilter.IsDelete != null)
            {
                packageSerVMs = packageSerVMs.AsEnumerable()
            .Where(x => x.IsDelete == packageSpendFilter.IsDelete)
            .AsQueryable(); // 转换回 IQueryable
            }
            //需要过滤后没做分页前的所有数据条数
            count = packageSerVMs.Count();
            //做分页
            if (packageSpendFilter.page != 0 && packageSpendFilter.limit != 0)
                return packageSerVMs.OrderBy(x => x.Id).Skip((packageSpendFilter.page - 1) * packageSpendFilter.limit).Take(packageSpendFilter.limit);
            else
                return packageSerVMs;
        }
        #endregion
    }
}
